﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SQLite;
using System.Web.Configuration;

//使用方法：
//using System;
//using Niunan.Tg029.Hr.Utility;


//namespace Niunan.Tg029.Hr.Web.test
//{
//    public partial class test_sqlite : System.Web.UI.Page
//    {
//        SQLiteHelper helper = new SQLiteHelper();


//        protected void Page_Load(object sender, EventArgs e)
//        {


//        }


//        // 创建表
//        protected void btnCreate_Click(object sender, EventArgs e)
//        {
//            string sql = "CREATE  TABLE tba (id INTEGER PRIMARY KEY  NOT NULL , content TEXT, createdate DATETIME DEFAULT CURRENT_TIMESTAMP)";
//            helper.CreateCommand(sql);
//            bool b = helper.ExecuteNonQuery();
//            Response.Write("创建表成功，" + DateTime.Now + "，" + b);
//        }


//        // 插入记录
//        protected void btnInsert_Click(object sender, EventArgs e)
//        {
//            string content = txtContent.Text.Trim();
//            if (content.Length == 0)
//            {
//                Response.Write("内容为空！");
//                return;
//            }


//            helper.CreateCommand("INSERT INTO tba(content) VALUES(@name);SELECT LAST_INSERT_ROWID();");
//            helper.AddParameter("@name", content);


//            string str = helper.ExecuteScalar();


//            Response.Write("插入成功，" + DateTime.Now + "，返回的ID：" + str);
//        }


//        // 查看
//        protected void btnSelect_Click(object sender, EventArgs e)
//        {
//            string sql = "SELECT * FROM tba";
//            helper.CreateCommand(sql);
//            gv.DataSource = helper.ExecuteQuery();
//            gv.DataBind();
//        }


//        // 取最大ID
//        protected void btnGetMaxID_Click(object sender, EventArgs e)
//        {
//            // 使用LINQ的方法从DATATABLE中获取某一字段的最大值
//            //string sql = "SELECT id,content,createdate FROM tba";
//            //helper.CreateCommand(sql);
//            //DataTable dt = helper.ExecuteQuery();
//            //object obj = dt.AsEnumerable().Select(x => x["id"]).Max();


//            // 使用SQL语句获取最大ID
//            string sql = "SELECT MAX(id) FROM tba";
//            helper.CreateCommand(sql);
//            object obj = helper.ExecuteScalar();
//            Response.Write("最大ID：" + obj.ToString() + "，" + DateTime.Now);
//        }
//    }
//}



namespace GttsWebLibrary.SQLLite
{
    /// <summary>
    /// 
    /// </summary>
    public class SQLiteHelper
    {
         private SQLiteConnection conn = null;
        private SQLiteCommand cmd = null;
        private SQLiteDataReader sdr = null;


        public SQLiteHelper()
        {
            string connStr = "";
            //TODO:SQLLite(应该添加) string connStr = WebConfigurationManager.ConnectionStrings["boziconnstr"].ToString();
            //string connStr = "Data Source=" + HttpContext.Current.Server.MapPath("~/App_Data/fdm.sqlite");
            conn = new SQLiteConnection(connStr);
        }


        /// <summary>创建Command对象
        /// 
        /// </summary>
        /// <param name="sql">SQL语句</param>
        public void CreateCommand(string sql)
        {
            conn.Open();
            cmd = new SQLiteCommand(sql, conn);
        }


        /// <summary>添加参数
        /// 
        /// </summary>
        /// <param name="paramName">参数名称</param>
        /// <param name="value">值</param>
        public void AddParameter(string paramName, object value)
        {
            cmd.Parameters.Add(new SQLiteParameter(paramName, value));
        }


        /// <summary>执行不带参数的增删改SQL语句
        ///  
        /// </summary>
        /// <param name="cmdText">增删改SQL语句</param>
        /// <param name="ct">命令类型</param>
        /// <returns></returns>
        public bool ExecuteNonQuery()
        {
            int res;
            try
            {
                res = cmd.ExecuteNonQuery();
                if (res > 0)
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return false;
        }


        /// <summary>执行查询SQL语句
        ///  
        /// </summary>
        /// <param name="cmdText">查询SQL语句</param>
        /// <returns></returns>
        public DataTable ExecuteQuery()
        {
            DataTable dt = new DataTable();
            using (sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
            {
                dt.Load(sdr);
            }
            return dt;
        }


        /// <summary>分页  
        ///   
        /// </summary>  
        /// <param name="tblName">表名</param>  
        /// <param name="fldName">字段名</param>  
        /// <param name="OrderfldName">排序字段名</param>  
        /// <param name="OrderType">排序方式：asc或者desc</param>  
        /// <param name="strWhere">条件，不用加where</param>  
        /// <param name="PageSize">页大小</param>  
        /// <param name="PageIndex">页索引</param>  
        /// <returns></returns> 
        public DataTable FengYe(string tblName, string fldName, string OrderfldName, string OrderType, string strWhere, int PageSize, int PageIndex)
        {
            DataTable dt = new DataTable();
            int start = (PageIndex - 1) * PageSize;
            string sql = "";
            if (!string.IsNullOrEmpty(strWhere))
            {
                // 条件不为空
                sql = string.Format("select {0} from {1} where {2} order by {3} {4} limit {5},{6}", fldName,
                    tblName, strWhere, OrderfldName, OrderType, start, PageSize);
            }
            else
            {
                // 条件为空
                sql = string.Format("select {0} from {1} order by {2} {3} limit {4},{5}", fldName,
                    tblName, OrderfldName, OrderType, start, PageSize);
            }
            CreateCommand(sql);
            dt = ExecuteQuery();
            return dt;
        }


        /// <summary>返回查询SQL语句查询出的结果的第一行第一列的值
        /// 
        /// </summary>
        /// <returns></returns>
        public string ExecuteScalar()
        {
            string res = "";
            try
            {
                object obj = cmd.ExecuteScalar();
                if (obj != null)
                {
                    res = obj.ToString();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
            }
            return res;
        }


    }
}